
mysqlimport --local -uroot -p*************  big_dump.sql


C:\freewares\MySQL5.0\bin\mysql global -uroot -p*************


/* base brut */
mysqlimport --local global dicobrut.txt -uroot -p*************


   

update dicobrut set def_mot_fr = "$" where trim(def_mot_fr) = "";
SELECT * from dicobrut order by motgb,cat_mot INTO OUTFILE 'c:/dicotri.txt' ;



create table dicobrut
(
motgb varchar(30) not null
, cat_mot varchar(10)
, def_mot_fr varchar(250) 
, def2_mot_fr varchar(250)
, def3_mot_fr varchar(250)
, def4_mot_fr varchar(250)
, INDEX motgb (motgb,cat_mot) 
) ;



create table global.modele
(
id_modele int not null PRIMARY KEY
, lb_modele varchar(250)
, interet int
,type int   
);

create procedure p (in x int) begin ; select * from mots ; end ;



show table status like 'drupal_sessions';

show tables; 

----------------------------------------------------- CHAT --------------------------


create table mots_as_rel
(
id_mot1 int 
,id_mot2 int 
,id_relation int 
,INDEX  index1 (id_mot1,id_mot2 ) 
,INDEX  index2 (id_mot2,id_mot1 ) 
) ;


create table mots_as
(
id_mot int PRIMARY KEY
,lib_mot varchar(80) character set utf8 
,type_mot int 
,UNIQUE INDEX  id_mot (id_mot) 
, UNIQUE INDEX  lib_mot (lib_mot) 
) ;


create table chat
(
id_phrase int AUTO_INCREMENT PRIMARY KEY
,ip varchar(15) not null
,phrase varchar(255)
,date_phr datetime 
,UNIQUE INDEX  id_phrase (id_phrase) 
) ;



create table chat
(
id_phrase int AUTO_INCREMENT PRIMARY KEY
,ip varchar(15) not null
,phrase varchar(255)
,date_phr datetime 
,UNIQUE INDEX  id_phrase (id_phrase) 
) ;


/* exporte tout */
mysqldump -uroot -p*************  global > global.sql
mysqldump -uroot -p************* DATABASE > database.sql


mysqldump --no-data -uroot -p*************  global lao  > create.sql


insert into chat(ip,phrase) values ("121.123.111.255", "c est un test")
insert into chat(ip,phrase,date_phr) values ("121.123.111.255", "c est un test", now()) ;
insert into chat(ip,phrase,date_phr) values ("121.123.111.255", "test 3", now()) ;



CREATE TABLE frlao (
  	lib_fr varchar(50) character set utf8 primary key
	,prononciation_fr varchar(50) character set utf8 
	,lib_lao varchar(20) character set utf8 default NULL
	,prononciation_thai varchar(20) character set utf8 default NULL
	,lib_thai varchar(20) character set utf8 default NULL
) ;


CREATE TABLE fr_mots (
  	id_mot  int AUTO_INCREMENT PRIMARY KEY
	,lib_fr varchar(50) character set utf8 
	,UNIQUE INDEX  lib_fr (lib_fr) 
) ;

CREATE TABLE trad_mots_fr (
  	id_mot  int 
	,lib_trad varchar(50) character set utf8 
	,ordre int
	,id_langue int
	,index main (id_mot, id_langue,ordre)
) ;


drop table type_mesure_sante ;
CREATE TABLE type_mesure_sante (
  	id_type_mesure  int PRIMARY KEY
	,lib_type_mesure varchar(50) character set utf8 
	,desc_type_mesure varchar(250) character set utf8 
	,lb_mesure1 varchar(50) character set utf8 
	,lb_mesure2 varchar(50) character set utf8 
	,lb_mesure3 varchar(50) character set utf8 
	,lb_mesure4 varchar(50) character set utf8 
	,unite_mesure int
	,nb_mesures int
	,UNIQUE INDEX  lib_type_mesure (lib_type_mesure) 
) ;


CREATE TABLE unite_mesure (
  	id_unite_mesure  int AUTO_INCREMENT PRIMARY KEY
	,lib_unite_mesure varchar(50) character set utf8 
) ;

CREATE TABLE mesures_sante (
 	id_mesure  int AUTO_INCREMENT PRIMARY KEY
	,id_type_mesure int
	,date_mesure datetime
	,mesure1 int
	,mesure2 int
	,mesure3 int
	,mesure4 int
	,cmesure1 varchar(200)
	,commentaire varchar(200) character set utf8
) ;

DROP TABLE not_found_words ;

DROP TABLE url ;


CREATE TABLE url (
 	id_url  int AUTO_INCREMENT PRIMARY KEY
	,url varchar(200)
	,statut_url int
  ,date_visite datetime
) ;

CREATE TABLE not_found_words (
 	id_not_found  int AUTO_INCREMENT PRIMARY KEY
	,lib_not_found varchar(60)
	,nb_occurences BIGINT UNSIGNED
	,conserver int default 0	
	,index nb_occurences (nb_occurences)
) ;

DROP TABLE session ;
CREATE TABLE session (
 	id_session  int AUTO_INCREMENT PRIMARY KEY
	,id_url int
  ,id_theme int
  ,date_mesure datetime
	,commentaire varchar(200) character set utf8
	,nb_url_scannes int
	,nb_phrases int
	,nb_mots int
	,profondeur_scan int
	,filtre varchar(200) 
) ;



DROP TABLE words_themes ;

CREATE TABLE words_themes (
 	id_words  int 
	,id_theme int
	,nb_occurences BIGINT UNSIGNED
	,UNIQUE INDEX  ix_pal (id_theme, id_words) 
) ;

CREATE TABLE session_url (
 	id_session  int 
	,id_url int
	,UNIQUE INDEX  ix_pal (id_session, id_url) 
) ;

CREATE TABLE user_param (
 	id_user  int 
	,param varchar(40) character set utf8
	,valeur varchar(255) character set utf8 
	,UNIQUE INDEX  ix_up (id_user, param) 
) ;

INSERT INTO user_param (id_user, param, valeur) VALUES (1,'FiltreSante','') ;

CREATE TABLE words_session (
 	id_words  int 
	,id_session int
	,nb_occurences int
	,UNIQUE INDEX  ix_ws (id_session, id_words) 
) ;

drop table domaines  ;
CREATE TABLE domaines (
 	id_domaine  int 
	,lib_domaine varchar(40) character set utf8
	,UNIQUE INDEX  ix_id_dom (id_domaine) 
) ;



select * from words_themes ;

CREATE TABLE url_etudes (
 	id_url  int 
	,id_etude int
) ;



CREATE TABLE etudes (
 	id_etude  int AUTO_INCREMENT PRIMARY KEY
	,lib_etude varchar(60)
	,type_etude int
) ;

--ex : roman, auteur, theme as cuisine
DROP TABLE themes ;
CREATE TABLE themes (
 	id_theme  int PRIMARY KEY
	,id_domaine int
  ,lib_theme varchar(60)		
) ;


CREATE TABLE personnes (
 	id_personne  int PRIMARY KEY
	,prenom_personne varchar(60)		
  ,nom_personne varchar(60)		
  ,initiales varchar(5)		
) ;


INSERT INTO personnes (id_personne,prenom_personne,nom_personne,initiales) VALUES (1,'Jean Christophe','Blanchard','JCB') ;
INSERT INTO personnes (id_personne,prenom_personne,nom_personne,initiales) VALUES (2,'Xuehui','Xie','XX') ;
INSERT INTO personnes (id_personne,prenom_personne,nom_personne,initiales) VALUES (3,'John-Christopher','Blanchard--Xie','JCB') ;


INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (2,6,'Gastronomie') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (3,6,'Informatique') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (4,7,'Cinema') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (5,6,'Sante') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (6,7,'BD') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (7,7,'Sports') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (8,0,'Test') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (9,7,'Art Martial') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (10,5,'Astronomie') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (11,0,'Informations') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (12,6,'M�decine') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (13,7,'Musique') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (14,8,'Marc Twain') ;
INSERT INTO themes (id_theme,id_domaine,lib_theme) VALUES (15,1,'Psychologie') ;


INSERT INTO domaines (id_domaine,lib_domaine) VALUES (0,'Generalites') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (1,'Philosophie/Psychologie') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (2,'Religion') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (3,'Sciences sociales') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (4,'Langues') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (5,'Sciences') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (6,'Techniques') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (7,'Arts') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (8,'Litt�rature') ;
INSERT INTO domaines (id_domaine,lib_domaine) VALUES (9,'Geographie/Histoire') ;


insert into unite_mesure (lib_unite_mesure) values ('Kg') ;
insert into unite_mesure (lib_unite_mesure) values ('Min Hg') ;
insert into unite_mesure (lib_unite_mesure) values ('cm') ;
insert into unite_mesure (lib_unite_mesure) values ('g/l') ;
insert into unite_mesure (lib_unite_mesure) values ('puls/min') ;
insert into unite_mesure (lib_unite_mesure) values ('mg/l') ;
insert into unite_mesure (lib_unite_mesure) values ('ml/min') ;


delete from type_mesure_sante ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ) values (1,'Poids','',1,1,'') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2) values (2,'TA','',2,2,'Valeur haute', 'Valeur basse') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (3,'Bicep','',3,2,'Contract�', 'D�tendu') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (4,'Tour taille','',3,1,'Valeur', '') ;



insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (5,'Glyc�mie','',4,1,'Valeur', '') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (6,'Cholest�rol�mie','',4,1,'Valeur', '') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (7,'Triglyc�rides','',4,1,'Valeur', '') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (8,'Cr�atinin�mie','',6,1,'Valeur', '') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (9,'Pouls','',5,1,'Valeur', '') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (10,'Clairance creatinine','',7,1,'Valeur', '') ;
insert into type_mesure_sante (id_type_mesure,lib_type_mesure,desc_type_mesure,unite_mesure,nb_mesures,lb_mesure1 ,lb_mesure2 ) values (11,'Taille','',3,1,'Valeur', '') ;





select motgb,words_themes.nb_occurences  from words_themes, words 
where words_themes.id_words = words.id_words 
and id_theme = 10
and words_themes.nb_occurences > 2
order by words_themes.nb_occurences asc
;

delete from words_themes where id_theme = 10


select * from not_found_words order by nb_occurences DESC LIMIT 100 ;
select * from words order by nb_occurences DESC LIMIT 100 ;

select * from words LIMIT 100 ;
select motgb,nb_occurences from words WHERE nb_occurences > 2 order by nb_occurences DESC  ;
select motgb,nb_occurences from words WHERE nb_occurences > 2 order by nb_occurences   ;
select count(*) from words WHERE nb_occurences > 10   ;
select motgb,nb_occurences from words WHERE nb_occurences > 20 order by nb_occurences   ;

select motgb,nb_occurences from words WHERE motgb like 'science'  ;

update not_found_words set lib_not_found = trim(lib_not_found) ;
update words set motgb = REPLACE(motgb,'\n','') where motgb like 'Web';

update words set motgb = LOWER(motgb) where motgb like 'Web';
update words set motgb = TRIM(motgb) ;
select motgb from words where motgb like 'Web';


select id_words,motgb,cat_word from words where id_words > 110890 order by id_words DESC ;


select * FROM words_session ;
select * FROM session ;

truncate not_found_words ;
truncate table words_session ; 
truncate table session ; 
truncate table url ; 
update words set nb_occurences = 0 ;
update not_found_words set nb_occurences = 0 ;

delete from not_found_words ;

mysqldump -uroot -p*************  global phrases_trad  > phrases_trad.sql
mysqldump -uroot -p*************  global phrases_trad  > phrases_trad.sql


ALTER TABLE words DROP COLUMN nb_occurences;
ALTER TABLE words ADD nb_occurences BIGINT UNSIGNED NOT NULL DEFAULT 0 ;
ALTER TABLE words ADD origin smallint NOT NULL DEFAULT 0 ;

ALTER TABLE words ADD INDEX (nb_occurences) ;

ALTER TABLE phrases_trad ADD niveau_diff smallint NOT NULL DEFAULT 0 ;

ALTER TABLE phrases_trad ADD vocabulaire smallint NOT NULL DEFAULT 0 ;

ALTER TABLE phrases_trad ADD grammaire smallint NOT NULL DEFAULT 0 ;

ALTER TABLE phrases_trad ADD temps smallint NOT NULL DEFAULT 0 ;


ALTER TABLE not_found_words ADD conserver int default 0 ;

alter table mesures_sante modify mesure1 
,mesure1 int
	,mesure2 int
	,mesure3 int
	,mesure4 int


ALTER TABLE phrases_trad DROP COLUMN language_1  ;




ALTER TABLE session DROP COLUMN date_mesure  ;
ALTER TABLE session ADD date_session DATE ;

ALTER TABLE phrases_trad ADD language_1 char(1) NOT NULL default 'F' ;
ALTER TABLE phrases_trad ADD language_2 char(1) NOT NULL default 'G' ;
ALTER TABLE phrases_trad ADD language_3 char(1) ;
ALTER TABLE phrases_trad ADD sound tinyint NOT NULL default 0 ;

select * from phrases_trad where  id_phrase_trad > 310 order by id_phrase_trad  ;

select note_diff from phrases_trad  where note_diff > 10 ;

ALTER TABLE phrases_trad ADD grammaire   ;

ALTER TABLE phrases_trad ADD phrase_fr varchar(255) character set utf8  ;

ALTER TABLE modele ADD author varchar(255) character set utf8  ;

ALTER TABLE phrases_trad ADD phrase_ch varchar(255) character set utf8  ;

ALTER TABLE phrases_trad ADD phrase_pinyin varchar(255) character set utf8  ;


ALTER TABLE words ADD freq_words_dim int default 0 ;
update  mesures_sante  set date_mesure = '03/03/2005'';

update words set freq_words_dim = nb_occurences / 17.22 where nb_occurences > 0;
update words_themes set freq_wordtheme_dim = nb_occurences / 0.32253 where id_theme = 9;
update words_themes set freq_wordtheme_dim = nb_occurences / 0.036 where id_theme = 10;

select sum(nb_occurences) from words ;
select sum(nb_occurences) from words_session where id_session = 12;


update words,words_session set words.nb_occurences = words.nb_occurences - words_session.nb_occurences where words_session.id_words = words.id_words and words_session.id_session = 12 ;

update words set words.nb_occurences = 62 where words.id_words = 112103;

select words_session.* from words,words_session where words.id_words = 112103 and words_session.id_words = words.id_words and words_session.id_session = 12 ;

select nb_occurences from words where words.id_words = 112103 ;

select count(*) from phrases_trad where phr_gb is null




ALTER TABLE phrases_trad DROP COLUMN phr_gb  ;


----------------------------------------------------------------------------------------------
